package com.elite.groovy.dev;

import com.elite.groovy.kh.TxConfigPro;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

/**
 * @projectName: ngsapitask-davco
 * @package: com.elite.groovy.dev
 * @className: FixMessage
 * @author: LiJT
 * @description: TODO
 * @date: 2024-05-13 16:00
 * @version: 1.0
 */
public class FixMessage {
    private static final Log log = LogFactory.getLog(TestTao.class);

    public static void main(String[] args) throws Exception {
        JdbcTemplate jt = TxConfigPro.getJdbcTemplate();

        List<Map<String, Object>> Chat_Message_List = jt.queryForList("Select Id, Chat_User_Id, Chat_Session_Id, Post_Time, From_Unixtime(Post_Time / 1000) As Send_Time From Chat_Message Where Ai_Text Is Null And Post_Time > Unix_Timestamp('2024-01-11 20:37:24') * 1000 Order By Post_Time Desc Limit 1000");

        for (int i = 0; i < Chat_Message_List.size(); i++) {
            // 获取Chat_Message信息
            String Chat_Session_Id = Chat_Message_List.get(i).get("Chat_Session_Id").toString();
            String Post_Time = Chat_Message_List.get(i).get("Post_Time").toString();
            String Send_Time = Chat_Message_List.get(i).get("Send_Time").toString();

            // 通过SessionID找到对应的坐席
            List<Map<String, Object>> Chat_Session_List = jt.queryForList("Select Agent_Id, Start_Time, Client_Id, Perm_Flag, Last_Queue_Id From Chat_Session Where Id = ?", Chat_Session_Id);
            log.debug(i + "# Chat_Session_List:" + Chat_Session_List);
            if (Chat_Session_List.size() > 0) {

                String Client_Id = Chat_Session_List.get(0).get("Client_Id").toString();

                // 找到用户ID
                List<Map<String, Object>> Customer_List = jt.queryForList("Select Customer_Guid, Customer_Id From Customer Where Customer_Guid = ?", Client_Id);
                log.debug(i + "# Customer_List:" + Customer_List);
                if (Customer_List.size() > 0) {
                    String Customer_Id = Customer_List.get(0).get("Customer_Id").toString();

                    // 找到此前进线历史
                    List<Map<String, Object>> Chat_Inparams_List = jt.queryForList("Select Rec_Guid, Update_Time, Staff_Id From Dg_Chat_Inparams Where Update_Time <= ? And Userid = ? Order By Update_Time Desc Limit 1", Send_Time, Customer_Id);
                    log.debug(i + "# Chat_Inparams_List:" + Chat_Inparams_List);
                    if (Chat_Inparams_List.size() > 0) {
                        String Rec_Guid = Chat_Inparams_List.get(0).get("Rec_Guid").toString();
                        String Update_Time = Chat_Inparams_List.get(0).get("Update_Time").toString();

                        // 更新标记表
                        int update_Num = jt.update("Update Chat_Message Set Ai_Text = ? Where Chat_Session_Id = ? And Post_Time > Unix_Timestamp(?) * 1000 And Post_Time <= ?", Rec_Guid, Chat_Session_Id, Update_Time, Post_Time);

                        // 更新统计数据
                        String totalSQL = "Update Dg_Chat_Inparams R1,\n" + "       (Select R1.Rec_Guid,\n" + "               R3.Id,\n" + "               Count(*) As Chatnumber,\n" + "               Sum(If(Length(R2.Chat_User_Id) > 6, 1, 0)) As Custnumber,\n" + "               Sum(If(Length(R2.Chat_User_Id) <= 6, 1, 0)) As Staffnumber,\n" + "               Max(R3.Perm_Flag) As Perm_Flag\n" + "          From Dg_Chat_Inparams R1\n" + "          Left Join Chat_Message R2\n" + "            On R1.Rec_Guid = R2.Ai_Text\n" + "          Left Join Chat_Session R3\n" + "            On R2.Chat_Session_Id = R3.Id\n" + "         Where R2.Ai_Text Is Not Null\n" + "           And R1.Rec_Guid = ?\n" + "         Group By R1.Rec_Guid, R3.Id) R2\n" + "   Set R1.Chatnumber = R2.Chatnumber, R1.Custnumber = R2.Custnumber, R1.Staffnumber = R2.Staffnumber\n" + " Where R1.Rec_Guid = R2.Rec_Guid And R1.Rec_Guid = ?";
                        int totalNum = jt.update(totalSQL, Rec_Guid, Rec_Guid);

                        if (update_Num > 0) {
                            log.debug(i + "# Chat_Message_List Map:" + Chat_Message_List.get(i));
                            log.debug(i + "# 更新标记Chat_Message Num:" + update_Num);
                            log.debug(i + "# 更新标记Chat_Message totalNum:" + totalNum);

                        } else {
                            log.debug(i + "# Chat_Message_List Map:" + Chat_Message_List.get(i));
                        }
                    } else {
                        log.warn(i + "# 未找到 找到此前进线历史Chat_Inparams_List:" + Chat_Message_List.get(i));
                    }
                } else {
                    log.warn(i + "# 未找到 找到用户ID Customer_List:" + Chat_Message_List.get(i));
                }
            } else {
                log.warn(i + "# 未找到 SessionID找到对应的坐席:" + Chat_Message_List.get(i));
            }

        }

    }

}
